`:top
`!SQLAlchemy`! is an `F33f`_`[open-source`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Open_source]`_`f `F33f`_`[Python`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Python_(programming_language)]`_`f library that provides an `F33f`_`[SQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL]`_`f toolkit (called "SQLAlchemy Core") and an `F33f`_`[object–relational mapper`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Object–relational_mapping]`_`f (ORM) for database interactions. It allows developers to work with databases using Python objects, enabling efficient and flexible database access.
>>Contents
• `F0af`_`[Description`#description]`_`f
• `F0af`_`[History`#history]`_`f
• `F0af`_`[Example`#example]`_`f
• `F0af`_`[Schema definition`#schema-definition]`_`f
• `F0af`_`[Data insertion`#data-insertion]`_`f
• `F0af`_`[Querying`#querying]`_`f
• `F0af`_`[See also`#see-also]`_`f
• `F0af`_`[References`#references]`_`f
-─
>>Description
SQLAlchemy offers tools for `F33f`_`[database schema`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Database_schema]`_`f generation, querying, and object-relational mapping. Key features include:
• A comprehensive embedded `F33f`_`[domain-specific language`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Domain-specific_language]`_`f for SQL in Python called "SQLAlchemy Core" that provides means to construct and execute SQL queries.
• A powerful ORM that allows the mapping of Python classes to database tables.
• Support for database schema migrations.
• Compatibility with multiple database backends.
• Tools for `F33f`_`[database connection`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Database_connection]`_`f pooling and transaction management.
>>History
SQLAlchemy was first released in February 2006. It has evolved to include a wide range of features for database interaction and has gained popularity among Python developers. Notable versions include:
• Version 0.1 (2006):`:cite-ref-5[`F5bf`_`[5`#cite-note-5]`_`f] Initial release.
• Version 1.0 (2015):`:cite-ref-6[`F5bf`_`[6`#cite-note-6]`_`f] Major enhancements in ORM and SQL expression language.
• Version 1.4 (2021):`:cite-ref-7[`F5bf`_`[7`#cite-note-7]`_`f] Introduction of a new ORM `F33f`_`[API`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=API]`_`f.
>>Example
The following example represents an n-to-1 relationship between movies and their directors. It is shown how user-defined Python classes create corresponding database tables, how instances with relationships are created from either side of the relationship, and finally how the data can be queried — illustrating automatically generated SQL queries for both `F33f`_`[lazy`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Lazy_loading]`_`f and eager loading.
>>>Schema definition
Creating two Python classes and corresponding database tables in the DBMS:
`B100`F9d9from sqlalchemy import *`f`b
`B100`F9d9from sqlalchemy.ext.declarative import declarative_base`f`b
`B100`F9d9from sqlalchemy.orm import relation, sessionmaker`f`b
`B100`F9d9`f`b
`B100`F9d9Base = declarative_base()`f`b
`B100`F9d9`f`b
`B100`F9d9class Movie(Base):`f`b
`B100`F9d9 __tablename__ = "movies"`f`b
`B100`F9d9`f`b
`B100`F9d9 id = Column(Integer, primary_key=True)`f`b
`B100`F9d9 title = Column(String(255), nullable=False)`f`b
`B100`F9d9 year = Column(Integer)`f`b
`B100`F9d9 directed_by = Column(Integer, ForeignKey("directors.id"))`f`b
`B100`F9d9`f`b
`B100`F9d9 director = relation("Director", backref="movies", lazy=False)`f`b
`B100`F9d9`f`b
`B100`F9d9 def __init__(self, title=None, year=None):`f`b
`B100`F9d9 self.title = title`f`b
`B100`F9d9 self.year = year`f`b
`B100`F9d9`f`b
`B100`F9d9 def __repr__(self):`f`b
`B100`F9d9 return f"Movie({self.title}, {self.year}, {self.director})"`f`b
`B100`F9d9`f`b
`B100`F9d9class Director(Base):`f`b
`B100`F9d9 __tablename__ = "directors"`f`b
`B100`F9d9`f`b
`B100`F9d9 id = Column(Integer, primary_key=True)`f`b
`B100`F9d9 name = Column(String(50), nullable=False, unique=True)`f`b
`B100`F9d9`f`b
`B100`F9d9 def __init__(self, name=None):`f`b
`B100`F9d9 self.name = name`f`b
`B100`F9d9`f`b
`B100`F9d9 def __repr__(self):`f`b
`B100`F9d9 return f"Director({self.name})"`f`b
`B100`F9d9`f`b
`B100`F9d9engine = create_engine("dbms://user:pwd@host/dbname")`f`b
`B100`F9d9Base.metadata.create_all(engine)`f`b
>>>Data insertion
One can insert a director-movie relationship via either entity:
`B100`F9d9Session = sessionmaker(bind=engine)`f`b
`B100`F9d9session = Session()`f`b
`B100`F9d9`f`b
`B100`F9d9m1 = Movie("Robocop", 1987)`f`b
`B100`F9d9m1.director = Director("Paul Verhoeven")`f`b
`B100`F9d9`f`b
`B100`F9d9d2 = Director("George Lucas")`f`b
`B100`F9d9d2.movies = [Movie("Star Wars", 1977), Movie("THX 1138", 1971)]`f`b
`B100`F9d9`f`b
`B100`F9d9try:`f`b
`B100`F9d9 session.add(m1)`f`b
`B100`F9d9 session.add(d2)`f`b
`B100`F9d9 session.commit()`f`b
`B100`F9d9except:`f`b
`B100`F9d9 session.rollback()`f`b
>>>Querying
`B100`F9d9alldata = session.query(Movie).all()`f`b
`B100`F9d9for somedata in alldata:`f`b
`B100`F9d9 print(somedata)`f`b
SQLAlchemy issues the following query to the DBMS (omitting aliases):
`B100`F9d9SELECT movies.id, movies.title, movies.year, movies.directed_by, directors.id, directors.name`f`b
`B100`F9d9FROM movies LEFT OUTER JOIN directors ON directors.id = movies.directed_by`f`b
The output:
`B100`F9d9Movie('Robocop', 1987L, Director('Paul Verhoeven'))`f`b
`B100`F9d9Movie('Star Wars', 1977L, Director('George Lucas'))`f`b
`B100`F9d9Movie('THX 1138', 1971L, Director('George Lucas'))`f`b
Setting `B100`F9d9lazy=True`f`b (default) instead, SQLAlchemy would first issue a query to get the list of movies and only when needed (lazy) for each director a query to get the name of the corresponding director:
`B100`F9d9SELECT movies.id, movies.title, movies.year, movies.directed_by`f`b
`B100`F9d9FROM movies`f`b
`B100`F9d9`f`b
`B100`F9d9SELECT directors.id, directors.name`f`b
`B100`F9d9FROM directors`f`b
`B100`F9d9WHERE directors.id = %s`f`b
>>See also
• `F33f`_`[SQLObject`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQLObject]`_`f
• `F33f`_`[Storm`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Storm_(software)]`_`f
• `F33f`_`[Pylons`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Pylons_project]`_`f
• `F33f`_`[TurboGears`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=TurboGears]`_`f
• `F33f`_`[Cubes (OLAP server)`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Cubes_(OLAP_server)]`_`f
>>References
`:cite-note-1`!1.`! Mike Bayer is the creator of SQLAlchemy and Mako Templates for Python.
`:cite-note-releases-2`!2.`! "Download - SQLAlchemy". SQLAlchemy. Retrieved 21 February 2015.
`:cite-note-wikidata-9986a6598bd01936424ce70755decbbe4c38ced6-v20-3`!3.`! "Release 2.0.41". 14 May 2025. Retrieved 1 June 2025.
`:cite-note-license-bitbucket-4`!4.`! "zzzeek / sqlalchemy / source / LICENSE". BitBucket. Retrieved 21 February 2015.
`:cite-note-5`!5.`! `F0af`_`[↑`#cite-ref-5]`_`f "0.1 Changelog — SQLAlchemy 2.0 Documentation". `*docs.sqlalchemy.org`*. Retrieved 2024-07-04.
`:cite-note-6`!6.`! `F0af`_`[↑`#cite-ref-6]`_`f "1.0 Changelog — SQLAlchemy 2.0 Documentation". `*docs.sqlalchemy.org`*. Retrieved 2024-07-04.
`:cite-note-7`!7.`! `F0af`_`[↑`#cite-ref-7]`_`f "1.4 Changelog — SQLAlchemy 2.0 Documentation". `*docs.sqlalchemy.org`*. Retrieved 2024-07-04.
`!Notes`!
• `:citerefgift2008`aGift, Noah (12 Aug 2008). "Using SQLAlchemy". `*Developerworks`*. IBM. Retrieved 8 Feb 2011.
• Rick Copeland, Essential SQLAlchemy, `F33f`_`[O'Reilly`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=O'Reilly_Media]`_`f, 2008, `F33f`_`[ISBN`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=ISBN_(identifier)]`_`f 0-596-51614-2
`c`F0af`_`[↑ Back to top`#top]`_`f`a